﻿CREATE PROCEDURE [dbo].[books_get_books_by_category]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @categoryId int = 0,
    @userId int = 0
AS

SELECT
    Books.BookId,
    Books.Title AS Book_Title,
    Books.Avatar AS Book_Avatar,
    Books.ShortContent,
    Books.[FileName],
    Rating = cast((SELECT Sum(Value) / Count(1)   From Ratings Where (Ratings.BookId = Books.BookId)) as nvarchar),
    IsRated = cast((SELECT Count(1) From Ratings Where (Ratings.BookId=Books.BookId AND Ratings.UserId=@userId)) as nvarchar)
FROM Books
    inner join BooksByCategory ON Books.BookId = BooksByCategory.BookId
WHERE BooksByCategory.CategoryId = @categoryId
ORDER BY
    CASE WHEN @column = 'Name' AND @direction = 1 THEN Books.Title END DESC,
    CASE WHEN @column = 'Name' AND @direction = 0 THEN Books.Title END ASC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 1 THEN Books.DateOfPublish END DESC,
    CASE WHEN @column = 'DateOfPublish' AND @direction = 0 THEN Books.DateOfPublish END ASC

OFFSET ((@page-1)*@size) ROWS
FETCH NEXT @size ROWS ONLY;

RETURN 0;